package com.dao;

import com.bean.课程档案;
import com.jdbc.DataSourceTool;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class 课程档案Dao {
    //增
    public boolean insertClass(课程档案 cla) throws SQLException {
        //通过DataSourceTool获取数据库链接对象
        Connection connection = DataSourceTool.getConnection();
        //定义sql
        String sql = "insert into 课程档案(课程代号,课程名称,类别,考核方式,学分,备注)" +
                "values (?,?,?,?,?,?)";
        //创建sql对象
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            //给sql赋值
            ps.setString(1,cla.get课程代号());
            ps.setString(2,cla.get课程名称());
            ps.setString(3, cla.get类别());
            ps.setString(4,cla.get考核方式());
            ps.setFloat(5, cla.get学分());
            ps.setString(6, cla.get备注());
            //执行sql
            int resul = ps.executeUpdate();
            //
            if(resul>0){
                return true;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            //关闭链接
            DataSourceTool.closeConnection(connection);
        }
        return false;
    }
    //删
    public boolean deleteClass(String 课程代号) throws SQLException {
        //通过DataSourceTool获取数据库链接对象
        Connection connection = DataSourceTool.getConnection();
        //定义sql
        String sql = "delete from 课程档案 where 课程代号 = ?";
        //创建sql对象
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            //给sql赋值
            ps.setString(1,课程代号);

            //执行sql
            int resul = ps.executeUpdate();
            //
            if(resul>0){
                return true;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            //关闭链接
            DataSourceTool.closeConnection(connection);
        }
        return false;
    }

    //改
    public boolean updateClass(课程档案 cla) throws SQLException {
        //通过DataSourceTool获取数据库链接对象
        Connection connection = DataSourceTool.getConnection();
        //定义sql
        String sql = "update 课程档案 set 课程名称=?,类别=?,考核方式=?,学分=?,备注=? where 课程代号=?; ";
        //创建sql对象
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            //给sql赋值
            ps.setString(1,cla.get课程名称());
            ps.setString(2, cla.get类别());
            ps.setString(3,cla.get考核方式());
            ps.setFloat(4, cla.get学分());
            ps.setString(5, cla.get备注());
            ps.setString(6,cla.get课程代号());
            //执行sql
            int resul = ps.executeUpdate();
            //
            if(resul>0){
                return true;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            //关闭链接
            DataSourceTool.closeConnection(connection);
        }
        return false;
    }
    //查 -- 返回结果集
    public List<课程档案> queryClass() throws SQLException {
        //创建集合用于保存数据
        List<课程档案> list = new ArrayList<>();
        //通过DataSourceTool获取数据库链接对象
        Connection connection = DataSourceTool.getConnection();
        //定义sql
        String sql = "select * from 课程档案;";
        //创建sql对象
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            //执行sql
            ResultSet rs = ps.executeQuery();
            //遍历结果集
            while(rs.next()){
                //创建一个课程档案类型的数据用于获取数据库中相同类型的数据
                课程档案 cla=new 课程档案();
                cla.set课程代号(rs.getString(1));
                cla.set课程名称(rs.getString(2));
                cla.set类别(rs.getString(3));
                cla.set考核方式(rs.getString(4));
                cla.set学分(rs.getFloat(5));
                cla.set备注(rs.getString(6));

                //把查询结果放入集合中
                list.add(cla);

            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            //关闭链接
            DataSourceTool.closeConnection(connection);
        }
        return list;
    }
}
